﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace SGArchiveSystem7xDB.Linq
{
    public  class T_BInfoDal
    {
        static SGArchiveSystem7xEntities conn;
        /// <summary>
        /// 添加借阅
        /// </summary>
        /// <param name="en"></param>
        /// <returns></returns>
        public static int Add_Brr(T_BInfo en, int type)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                conn.T_BInfo.Add(en);
                conn.SaveChanges();
                conn = new SGArchiveSystem7xEntities();
                if (type==(int)_BrrType._iType_纸质借阅) {
                    var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == en.BI_DH);
                    foreach (var n in query)
                    {
                        n.KFZT = (int)_ArchiveKFStatus._iStatus_被申请;
                    }
                    conn.SaveChanges();
                }
                return en.BI_ID;
            }
            catch(Exception ex)
            { return -1; }
        }
        /// <summary>
        /// 审批档案
        /// </summary>
        /// <param name="en"></param>
        /// <returns></returns>
        public static _ReturnValue SP_Archive(T_BInfo en)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.T_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID);
                var query1 = conn.V_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID).Single();
                var user = conn.T_User.Select(c => c).Where(c => c.U_ID == query1.BU_BUSERID).Single();
                string[] email = new string[1] { user.U_V01 };
                if (en.BI_SPVALUE == 1)
                    PrepareEmail("审批结果","你对档案为"+query1.BI_DH+"题名为"+query1.BI_TM +"档案的申请已经通过", email);
                else
                    PrepareEmail("审批结果", "你对档案为" + query1.BI_DH + "题名为" + query1.BI_TM + "档案的申请没有通过", email);
                foreach (var n in query)
                {
                    n.BI_SPDATE = en.BI_SPDATE;
                    n.BI_SPID = en.BI_SPID;
                    n.BI_SPNAME = en.BI_SPNAME;
                    n.BI_SPVALUE = en.BI_SPVALUE;
                    n.BI_SPSTATUS = en.BI_SPSTATUS;
                    if (query1.BU_TYPE == (int)_BrrType._iType_电子借阅 && en.BI_SPVALUE == (int)_BrrSPValue._iValue_审批通过)
                    {
                        n.BI_LQDATE = DateTime.Now;
                        n.BI_STATUS = (int)_BrrStatus._iStatus_借出;
                    }
                    if (query1.BU_TYPE == (int)_BrrType._iType_纸质借阅 && en.BI_SPVALUE == (int)_BrrSPValue._iValue_审批通过)
                    {
                        n.BI_STATUS = (int)_BrrStatus._iStatus_申请;
                    }
                    if(en.BI_SPVALUE == (int)_BrrSPValue._iValue_审批拒绝)
                    {
                        n.BI_STATUS = (int)_BrrStatus._iStatus_结束;
                        if (query1.BU_TYPE == (int)_BrrType._iType_纸质借阅)
                        {
                            var query2 = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == query1.BI_DH);
                            foreach (var cn in query2)
                            {
                                cn.KFZT =(int) _ArchiveKFStatus._iStatus_在库;
                            }
                            conn.SaveChanges();
                        }
                    }

                }
                conn.SaveChanges();
                return _ReturnValue.OK;
            }
            catch { return _ReturnValue.Err; }
        }
        /// <summary>
        /// 归还档案
        /// </summary>
        /// <param name="en"></param>
        /// <returns></returns>
        public static _ReturnValue GH_Archive(T_BInfo en)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.T_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID);
                foreach (var n in query)
                {
                    n.BI_GHDJID = en.BI_GHDJID;
                    n.BI_GHDJNAME = en.BI_GHDJNAME;
                    n.BI_SJGHDATE = DateTime.Now;
                    n.BI_D02 = DateTime.Now;
                    n.BI_STATUS = (int)_BrrStatus._iStatus_结束;
                }
                conn.SaveChanges();
                var query1 = conn.V_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID).Single();
                var query2 = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == query1.BI_DH);
                foreach (var cn in query2)
                {
                    cn.KFZT = (int)_ArchiveKFStatus._iStatus_在库;
                }
                conn.SaveChanges();
                return _ReturnValue.OK;
            }
            catch { return _ReturnValue.Err; }
        }
        /// <summary>
        /// 档案出库
        /// </summary>
        /// <param name="en"></param>
        /// <returns></returns>
        public static _ReturnValue CK_Archive(T_BInfo en)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.T_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID);
                foreach (var n in query)
                {
                    n.BI_CKID = en.BI_CKID;
                    n.BI_CKNAME = en.BI_CKNAME;
                    ///n.BI_LQDATE = DateTime.Now;
                    n.BI_D01 = DateTime.Now;
                    n.BI_STATUS = (int)_BrrStatus._iStatus_借出;
                }
                conn.SaveChanges();
                var query1 = conn.V_BInfo.Select(c => c).Where(c => c.BI_ID == en.BI_ID).Single();
                var query2 = conn.T_ArchiveAj.Select(c => c).Where(c => c.DH == query1.BI_DH);
                foreach (var cn in query2)
                {
                    cn.KFZT = (int)_ArchiveKFStatus._iStatus_借出;
                }
                conn.SaveChanges();
                return _ReturnValue.OK;
            }
            catch { return _ReturnValue.Err; }
        }
        /// <summary>
        /// 借阅信息
        /// </summary>
        /// <param name="brrstatus"> _iStatus_申请 = 0,_iStatus_借出 = 1,iStatus_结束 = 2</param>
        /// <param name="type">//1 = 电子 ，2 = 纸质</param>
        /// <param name="count"></param>
        /// <param name="start"></param>
        /// <param name="row"></param>
        /// <returns></returns>
        public static List<V_BInfo> Get_SPInfo(int brrstatus,int spstatus,int type,string name,string dh, string starttime, string endtime, string tm,int arid,out int count, int start = -1, int row = -1,int uid=-1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c);
                if (brrstatus > -1)
                {
                    query = query.Where(c => c.BI_STATUS == brrstatus);
                }
                if (spstatus > -1)
                {
                    if (spstatus == 4)
                    {
                        query = query.Where(c => c.BI_SPSTATUS == 0 || c.BI_SPSTATUS == 2 || c.BI_SPSTATUS == 3);
                    }
                    else
                        query = query.Where(c => c.BI_SPSTATUS == spstatus);
                }
                if (type > -1)
                    query = query.Where(c => c.BU_TYPE == type);
                if(name!=null&&name!="")
                    query = query.Where(c => c.BU_BUSERNAME.Contains(name));
                if (dh != null && dh != "")
                    query = query.Where(c => c.BI_DH.Contains(dh));
                if(arid>-1)
                    query = query.Where(c => c.BI_ARID==arid);
                if (tm != null && tm != "")
                    query = query.Where(c => c.BI_TM.Contains(tm));
                if (starttime != null && starttime != "")
                {
                    DateTime st = DateTime.Parse(starttime);
                    query = query.Where(c => c.BU_DATE >= st);
                }
                if (endtime != null && endtime != "")
                {
                    DateTime et = DateTime.Parse(endtime);
                    query = query.Where(c => c.BU_DATE <= et);
                }
                if(uid>-1)
                    query = query.Where(c => c.BU_BUSERID == uid);
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.BI_STATUS).Skip(start).Take(row).ToList();
                }
                else
                {
                    var list = query.ToList();
                    count = list.Count;
                    return list;
                }

            }
            catch { return null; }
        }
        public static List<V_BInfo> Get_OutInKFInfo(int status,string uname,string dh,string tm,out int count, int start = -1, int row = -1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c => c.BI_SPVALUE == 1 && c.BI_STATUS == status &&c.BU_TYPE==2);//审批状态通过，申请状态 _iStatus_申请 = 0  ,_iStatus_借出 = 1,
                if (uname != "" && uname != null)
                {
                    query = query.Where(c => c.BU_BUSERNAME.Contains(uname));
                }
                if (dh != "" && dh != null)
                {
                    query = query.Where(c => c.BI_DH.Contains(dh));
                }
                if (tm != "" && tm != null)
                {
                    query = query.Where(c => c.BI_TM.Contains(tm));
                }
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.BI_ID).Skip(start).Take(row).ToList();
                }
                else
                {
                    var list = query.ToList();
                    count = list.Count;
                    return list;
                }

            }
            catch { return null; }
        }

        /// <summary>
        /// 浏览信息
        /// </summary>
        /// <returns></returns>
        public static List<V_BInfo> Get_BeowseDZInfo(string uname, out int count, int start = -1, int row = -1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c=>c.BI_ATYPE == "1" && c.BI_CKNAME == uname);
                //1 = 电子 ，2 = 纸质
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.BI_ID).Skip(start).Take(row).ToList();
                }
                return query.ToList() ;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 字纸借出
        /// </summary>
        /// <returns></returns>
        public static List<V_BInfo> Get_BeowsePaperInfo(string rname, out int count, int start = -1, int row = -1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c => c.BI_ATYPE == "2"&&c.BI_CKNAME == rname);
                //1 = 电子 ，2 = 纸质
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.BI_ID).Skip(start).Take(row).ToList();
                }
                return query.ToList();
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        /// <summary>
        /// 发布准备及发送
        /// </summary>
        /// <param name="info">邮件主题</param>
        /// <param name="body">邮件内容</param>
        /// <param name="toMail">发送地址（null为）</param>"zrlinzhiqiang@126.com""zrlinzhiqian"
        public static int PrepareEmail(string info, string body, string[] toMail)
        {
            try
            {
                var pz = SGArchiveSystem7xDB.Linq.T_SyspzDal.Get_SysPZ("Email");
                if (pz.PZ_VALUE == "1")
                {
                    int count;
                    var listFromMail = SGArchiveSystem7xDB.Linq.T_EmailDal.Get_Email(0, out count).Single();
                    string senderServerIp = listFromMail.E_USER; //"smtp.126.com"; //"198.11.18.5"; //"smtp.126.com";   //使用163代理邮箱服务器（也可是使用qq的代理邮箱服务器，但需要与具体邮箱对相应）
                    string toMailAddress = ""; // n.Email_Addr;              //要发送对象的邮箱
                    string fromMailAddress = listFromMail.E_EMAIL;//"DAYXGL@126.com";//"zrlinzhiqiang@126.com";//listFromMail.E_EMAIL;//你的邮箱
                    string subjectInfo = info;                  //主题

                    string bodyInfo = body;  //以Html格式发送的邮件
                    int a = listFromMail.E_EMAIL.IndexOf('@');
                    string mailUsername = listFromMail.E_EMAIL.Substring(0, a);//;"DAYXGL";//"pop.126.com";//listFromMail.E_USER;              //登录邮箱的用户名
                    string mailPassword = listFromMail.E_PWD;// "YU1234";//"linzhiqiang";//listFromMail.E_PWD; //对应的登录邮箱的第三方密码（你的邮箱不论是163还是qq邮箱，都需要自行开通stmp服务）
                    string mailPort = "25";                      //发送邮箱的端口号
                                                                 // var listToMail = SGArchiveSystem7xDB.Linq.T_UserDal.GetUser(null,-1,-1 ,out count);
                    foreach (var nn in toMail)
                    {
                        toMailAddress = nn;//"931453369@qq.com";
                        SGArchiveSystem7xCommons.Email email = new SGArchiveSystem7xCommons.Email(senderServerIp, toMailAddress, fromMailAddress, subjectInfo, bodyInfo, mailUsername, mailPassword, mailPort, false, false);
                        email.Send();
                    }
                    return 1;
                }
                else
                    return 0;
            }
            catch
            {
                return -1;
            }

        }
        public static V_BInfo Get_Brr(int id)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c => c.BI_ID == id).Single();
                return query;
            }
            catch (Exception ex)
            {
                return null;
            }
        }
        public static _ReturnValue End_BRRArchive(int id)
        {
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c => c.BI_ID == id);
                foreach (var n in query)
                {
                    n.BI_SJGHDATE = DateTime.Now;
                    n.BI_STATUS = (int)_BrrStatus._iStatus_结束;
                }
                conn.SaveChanges();
                return _ReturnValue.OK;
        }
            catch { return _ReturnValue.Err; }
        }
        #region
        /// <summary>
        /// 借阅日志
        /// </summary>
        /// <returns></returns>
         public static List<V_BInfo> Get_OutInKFJieYue(int type, string uname, string dh, string tm,string stime,string etime, out int count, int start = -1, int row = -1)
        {
            count = 0;
            try
            {
                conn = new SGArchiveSystem7xEntities();
                var query = conn.V_BInfo.Select(c => c).Where(c => c.BI_SPVALUE == 1);
                if (type > -1 )
                {
                    query = query.Where(c =>c.BU_TYPE == type);
                }
                if (uname != "" && uname != null)
                {
                    query = query.Where(c => c.BU_BUSERNAME.Contains(uname));
                }
                if (dh != "" && dh != null)
                {
                    query = query.Where(c => c.BI_DH.Contains(dh));
                }
                if (tm != "" && tm != null)
                {
                    query = query.Where(c => c.BI_TM.Contains(tm));
                }
                if (stime != null && stime != "")
                {
                    DateTime st = DateTime.Parse(stime);
                    query = query.Where(c => c.BU_DATE >= st);
                }
                if (etime != null && etime != "")
                {
                    DateTime et = DateTime.Parse(etime);
                    query = query.Where(c => c.BU_DATE <= et);
                }
                if (start != -1 && row != -1)
                {
                    count = query.Count();
                    return query.OrderBy(c => c.BI_ID).Skip(start).Take(row).ToList();
                }
             
                else
                {
                    var list = query.ToList();
                    count = list.Count;
                    return list;
                }

            }
            catch { return null; }
        }
        #endregion

        /// <summary>
        /// 获取个人信息
        /// </summary>
        /// <param name="id">U_ID</param>
        /// <returns>1. 电子借阅的，2. 纸质借阅的， 3. 等待审批的， 4. 需要审批的</returns>
        public static List<int> Get_BrrSPInfo   (int id)
        {
            List<int> list = new List<int>();
            conn = new SGArchiveSystem7xEntities();
            var query = conn.V_BInfo.Select(c => c).Where(c => c.BU_BUSERID == id);
            //正在看的电子的
            int q = query.Where(c => c.BI_STATUS == 1 && c.BI_SPVALUE == 1 && c.BU_TYPE == 1).Count();
            list.Add(q);
            //正在看的纸质的
            int w = query.Where(c => c.BI_STATUS == 1 && c.BI_SPVALUE == 1 && c.BU_TYPE == 2).Count();// 
            list.Add(w);
            //等待审批的还没有通过
            int e = query.Where(c => c.BI_STATUS == 0 && c.BI_SPVALUE == 0).Count();
            list.Add(e);
            //需要审批的（管理员才拥有的权限，默认为0）
            int r = 0;
            var en = conn.V_User.Select(c => c).Where(c => c.U_ID == id).Single();
            if(en.U_I01==1)//部门管理员
            {
                var qu = conn.V_SP.Select(c => c).Where(c => c.SPF_UserDep == en.U_DEPID || c.SPF_AJDep == en.U_DEPID);
                qu = qu.Where(c => c.SPF_Status == 0);//0为未审批状态
                r = r + qu.Count();
            }
            string[] arr = en.ROLE_V01.Split(',');
            if(arr.Contains("8"))//档案管理员
            {
                var qu = conn.V_BInfo.Select(c => c).Where(c => c.BI_STATUS == 0 && c.BI_SPSTATUS == 0);
                r = r+ qu.Count();
            }
            list.Add(r);
            return list;
        }
        public static bool Get_BrrInfo(int brruid,string dh)
        {
            conn = new SGArchiveSystem7xEntities();
            var query = conn.V_BInfo.Select(c => c).Where(c => c.BU_BUSERID == brruid&&c.BI_DH==dh&&c.BI_STATUS!=2);
            if (query.Count() > 0)
                return true;
            else
                return false;
        }

        /// <summary>
        /// 取消出库并将档案设置为在库状态
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public static int EndCK(int id, string uname, int uid)
        {
            conn = new SGArchiveSystem7xEntities();
            try
            {
                var binfo = conn.T_BInfo.Select(c => c).Where(c => c.BI_ID == id).Single();

                var query = conn.T_ArchiveAj.Select(c => c).Where(c => c.ID == binfo.BI_ARCHIVEID);
                foreach (var n in query)
                {
                    if (n.KFZT != (int)SGArchiveSystem7xDB._ArchiveKFStatus._iStatus_被申请)
                        return 0;
                    else
                    {
                        n.KFZT = (int)SGArchiveSystem7xDB._ArchiveKFStatus._iStatus_在库;

                        binfo.BI_STATUS = (int)SGArchiveSystem7xDB._BrrStatus._iStatus_结束;
                        binfo.BI_SPSTATUS = (int)SGArchiveSystem7xDB._BrrSPStatus._iStatus_已审批;
                        binfo.BI_V05 = "取消出库";
                        T_LogArchiveInit en = new T_LogArchiveInit();
                        en.LOG_AID = n.ID;
                        en.LOG_ANAME = n.DH;
                        en.LOG_ARID = n.ARID;
                        en.LOG_DATE = DateTime.Now;
                        en.LOG_TYPE = "取消出库";
                        en.LOG_UID = uid;
                        en.LOG_UNAME = uname;
                        conn.T_LogArchiveInit.Add(en);
                    }
                }
                conn.SaveChanges();
                ///添加日志
                return 1;
            }
            catch { return -1; }
        }
    }
}
